package com.mes.service.warehouse;

import com.yy.base.common.dao.Dao;
import com.yy.base.util.StringUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.Date;
import java.util.List;
import java.util.Map;

@Service
public class SxkXjListService {
    @Autowired
    private Dao dao;


    //根据传递的机台编码查询基础数据
    public List<Map<String,Object>> selectSxListByCode (String code ) {
        String sql = "\tselect \n" +
                "\twl.NAME AS WLNAME,\n" +
                "\teq.code ,\n" +
                "\twl.m_product_id AS WLID,\n" +
                "\tys.NAME AS YSNAME,\n" +
                "\tys.emes_color_id AS YSID,\n" +
                "\tdw.NAME AS DWNAME,\n" +
                "\tdw.c_uom_id AS DWID,\n" +
                "\tSUM ( outs.qty ) AS QTYS,\n" +
                "\tNULL AS OUTQTYS \n" +
                "\tfrom eam_facilities eq , wms_storage_in_out outs , wms_storage_batch batch , emes_color ys ,c_uom dw, m_product wl\n" +
                "\twhere eq.eam_facilities_id = outs.data_id\n" +
                "\tand batch.m_product_id = wl.m_product_id\n" +
                "\tand outs.wms_storage_batch_id = batch.wms_storage_batch_id\n" +
                "\tand batch.emes_color_id = ys.emes_color_id\n" +
                "\tAND batch.c_uom_id = dw.c_uom_id \n" +
                "\tAND eq.CODE = '"+code+"' \n" +
                "\tGROUP BY\n" +
                "\twl.NAME,\n" +
                "\teq.code,\n" +
                "\tys.NAME,\n" +
                "\tdw.NAME,\n" +
                "\twl.m_product_id,\n" +
                "\tys.emes_color_id,\n" +
                "\tdw.c_uom_id" ;
       /* String sql = "SELECT  " +
                "  wl.NAME AS WLNAME,  " +
                "  wl.m_product_id as WLID , " +
                "  ys.NAME AS YSNAME,  " +
                "  ys.emes_color_id as YSID , " +
                "  dw.NAME AS DWNAME, " +
                "  dw.c_uom_id as DWID,  " +
                "  sum( outs.qty )  as QTYS ," +
                "  null as OUTQTYS " +
                "FROM  " +
                "  eam_facilities eq,  " +
                "  m_warehouse ck,  " +
                "  wms_storage_in_out outs,  " +
                "  wms_storage_batch batch,  " +
                "  emes_color ys,  " +
                "  c_uom dw,  " +
                "  m_product wl,  " +
                "  m_product_category fl   " +
                "WHERE  " +
                "  eq.eam_facilities_id = ck.eam_facilities_id   " +
                "  AND ck.m_warehouse_id = outs.m_warehouse_id   " +
                "  AND outs.wms_storage_batch_id = batch.wms_storage_batch_id   " +
                "  AND batch.emes_color_id = ys.emes_color_id   " +
                "  AND batch.m_product_id = wl.m_product_id   " +
                "  AND batch.c_uom_id = dw.c_uom_id   " +
                "  AND wl.m_product_category_id = fl.m_product_category_id   " +
                "  AND fl.  " +
                "VALUE  " +
                "  = 'SC-SX'   " +
                "  AND eq.CODE = '"+code+"'   " +
                "GROUP BY  " +
                "  wl.NAME,  " +
                "  ys.NAME,  " +
                "  dw.NAME,wl.m_product_id, ys.emes_color_id , dw.c_uom_id " ;*/

        return dao.query(sql);
    }


    //根据传过来的物料ID，颜色ID，尺码ID定位库存批次表的ID
    public List<Map<String,Object>> selectStorageBatch(String WLID, String COLORID) {
        String sql = "SELECT  " +
                "  batch.wms_storage_batch_id  as BATCHID " +
                " FROM  " +
                "  Wms_Storage_Batch batch   " +
                " WHERE  " +
                "  batch.m_product_id = '"+WLID+"'  " ;
        if(COLORID != null && !COLORID.equals("")){
            sql += "  AND batch.emes_color_id = '"+COLORID+"' " ;
        }


        return dao.query(sql);
    }

    //查询颜色
    public List<Map<String,Object>> selectColor() {
        String sql = " select  ys.emes_color_id as YSID, ys.name as YSNAME from emes_color  ys   " ;

        return dao.query(sql);
    }


    //根据传过来的设备编码查询仓库ID
    public List<Map<String,Object>> selectCkId(String code) {
        String sql = "SELECT  " +
                "  ck.m_warehouse_id as CKID  " +
                "FROM  " +
                "  m_warehouse ck,  " +
                "  eam_facilities eq   " +
                "WHERE  " +
                "  ck.eam_facilities_id = eq.eam_facilities_id   " +
                "  AND eq.CODE = '"+code+"'" ;
        return dao.query(sql);
    }

    //保存纱线库上架信息
    @SuppressWarnings("unchecked")
    public boolean saveStorageLists(String BATCHID, String HOUSEID, double  QTYS, String UOMID, String types,  String status, String adorgId, String adclientId, String createBy, String updatedBy, Date time) {
        boolean result = false;
        String uuid = StringUtil.getUUID();
        String lastRest = "Y";
        String sql =  "INSERT INTO Wms_Storage_In_Out (ISACTIVE,Wms_Storage_In_Out_ID,Wms_Storage_Batch_ID, M_Warehouse_ID,Qty,C_Uom_ID,Opt_Mode,STATUS,AD_ORG_ID,AD_CLIENT_ID,CREATED,CREATEDBY,UPDATED,UPDATEDBY) " +
                "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

        result = dao.executeSql(sql, lastRest,uuid,BATCHID,HOUSEID,-QTYS,UOMID,types,status,adorgId,adclientId,time,createBy,time,updatedBy) > 0;

        return result;
    }

    //查询CREATEBY和UPDATEBY的ID值
    public List<Map<String,Object>> getEquipmentputCreateby(String userIdtime){
        return dao.query("select CREATEDBY as CREATEDBY, UPDATEDBY as UPDATEDBY from ad_user where ad_user_id = ?",userIdtime);
    }


}
